﻿using System;
using System.Data;
using System.Collections.Generic;

using CoreLab.Oracle; //using Devart.Data.Oracle; //using Oracle.DataAccess.Client;
//using Oracle.DataAccess.Types;

using LMD.Constant;
using LMD.Lib.DataClass;
using LMD.Lib.Oracle;

namespace DBQuery
{
    /// <summary>
    /// Summary description for SLATable
    /// </summary>
    public static class SLATable
    {
        public static int SaveSLA(SLAData slaData)
        {
            DBLib dbl = new DBLib();
            List<OracleParameter> lsParams = new List<OracleParameter>();

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "out_new_id";
            lsParams[lsParams.Count - 1].Direction = ParameterDirection.Output;
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_MONTH";
            lsParams[lsParams.Count - 1].Direction = ParameterDirection.Input;
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            lsParams[lsParams.Count - 1].Value = slaData.iMonth;
            
            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_YEAR";
            lsParams[lsParams.Count - 1].Direction = ParameterDirection.Input;
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            lsParams[lsParams.Count - 1].Value = slaData.iYear;
    
            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_DELIVERED_DATE";
            lsParams[lsParams.Count - 1].Direction = ParameterDirection.Input;
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Date;
            lsParams[lsParams.Count - 1].Value = slaData.dtmDeliveredDate;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_T_FILE_UPLOAD_ID";
            lsParams[lsParams.Count - 1].Direction = ParameterDirection.Input;
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            if (slaData.iFile_Upload_ID < 0)
            {
                lsParams[lsParams.Count - 1].Value = DBNull.Value;
            }
            else
            {
                lsParams[lsParams.Count - 1].Value = slaData.iFile_Upload_ID;
            }

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_CONTACT_PERSON_ID";
            lsParams[lsParams.Count - 1].Direction = ParameterDirection.Input;
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            if (slaData.iContactPersonID < 0)
            {
                lsParams[lsParams.Count - 1].Value = DBNull.Value;
            }
            else
            {
                lsParams[lsParams.Count - 1].Value = slaData.iContactPersonID;
            }

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_NEW_CONTACT_PERSON_ID";
            lsParams[lsParams.Count - 1].Direction = ParameterDirection.Input;
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            if (slaData.iNewContactPersonID < 0)
            {
                lsParams[lsParams.Count - 1].Value = DBNull.Value;
            }
            else
            {
                lsParams[lsParams.Count - 1].Value = slaData.iNewContactPersonID;
            }

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_CREATE_BY";
            lsParams[lsParams.Count - 1].Direction = ParameterDirection.Input;
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
            lsParams[lsParams.Count - 1].Value = ICPUser.ID;

            String sxQuery = "pr_INSERT_T_SLA";

            dbl.ExecuteProcedure(sxQuery, lsParams.ToArray());
            
            return Convert.ToInt32(lsParams[0].Value);
        }
        public static void UpdateVisitData(CVData cvData)
        {
            DBLib dbl = new DBLib();

            OracleParameter prmID = new OracleParameter();
            prmID.ParameterName = "prmID";
            prmID.OracleDbType = OracleDbType.Number;
            prmID.Value = cvData.iCVID;

            OracleParameter prmVisitStatus = new OracleParameter();
            prmVisitStatus.ParameterName = "prmVisitStatus";
            prmVisitStatus.OracleDbType = OracleDbType.VarChar;
            prmVisitStatus.Value = "REALIZED";

            OracleParameter prmUpdateBy = new OracleParameter();
            prmUpdateBy.ParameterName = "prmUpdateBy";
            prmUpdateBy.OracleDbType = OracleDbType.VarChar;
            prmUpdateBy.Value = ICPUser.ID;

            OracleParameter prmResult = new OracleParameter();
            prmResult.ParameterName = "prmResult";
            prmResult.OracleDbType = OracleDbType.VarChar;
            if (String.IsNullOrEmpty(cvData.sxResult))
            {
                prmResult.Value = DBNull.Value;
            }
            else
            {
                prmResult.Value = cvData.sxResult;
            }

            OracleParameter prmFollowUp = new OracleParameter();
            prmFollowUp.ParameterName = "prmFollowUp";
            prmFollowUp.OracleDbType = OracleDbType.VarChar;
            if (String.IsNullOrEmpty(cvData.sxFollowUp))
            {
                prmFollowUp.Value = DBNull.Value;
            }
            else
            {
                prmFollowUp.Value = cvData.sxFollowUp;
            }

            OracleParameter prmFileUploadID = new OracleParameter();
            prmFileUploadID.ParameterName = "prmFileUploadID";
            prmFileUploadID.OracleDbType = OracleDbType.Number;
            prmFileUploadID.Value = cvData.iFile_Upload_ID;

            OracleParameter prmCustomerExpectation = new OracleParameter();
            prmCustomerExpectation.ParameterName = "prmCustomerExpectation";
            prmCustomerExpectation.OracleDbType = OracleDbType.Number;
            prmCustomerExpectation.Value = cvData.iP_Customer_Expectation_ID;

            String sxQuery =
            @"
            update
                T_CUSTOMER_VISIT
            set
                VISIT_STATUS = :prmVisitStatus,
                UPDATE_BY = :prmUpdateBy,
                UPDATE_DATE = sysdate,
                RESULT = :prmResult,
                FOLLOW_UP = :prmFollowUp,
                T_FILE_UPLOAD_ID = :prmFileUploadID,
                P_CUSTOMER_EXPECTATION_ID = :prmCustomerExpectation
            where
                T_CUSTOMER_VISIT_ID = :prmID";

            dbl.ExecuteNonQuery(sxQuery, prmID, prmVisitStatus, prmUpdateBy, prmResult,
                prmFollowUp, prmFileUploadID, prmCustomerExpectation);

            Cached.CustomerVisitTable cachedCV = new Cached.CustomerVisitTable();
            cachedCV.RemoveCache();
            
        }
    }
}